"""
    Author:
    Date: 2018-12-26

    短视频管理类
"""

from apps.gma import helpers as gma_helpers
from entities.consts import *
from utils import db, util


def get_video_count_by_auth_status(status, request=None):
    """ 根据审核状态获取视频数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS video_count
        FROM        cmf_vchat_videos AS v
        JOIN        cmf_users_auth AS a ON v.uid=a.uid
        JOIN        cmf_users AS u ON u.id=v.uid
        WHERE       {apps_condition} AND
                    v.auth_status={status} AND
                    a.status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['video_count']


def get_verified_videos():
    """ 根据审核状态获取视频数量 """
    sql = f'''
        SELECT      v.*
        FROM        cmf_vchat_videos AS v
        JOIN        cmf_users_auth AS a ON v.uid=a.uid
        WHERE       v.auth_status={VIDEO_AUTH_STATUS_AGREE} AND
                    a.status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_video_count_by_star(star, request=None):
    """ 根据视频星级获取视频数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS video_count
        FROM        cmf_vchat_videos AS v
        JOIN        cmf_users AS u ON u.id=v.uid
        WHERE       {apps_condition} AND
                    v.star={star} AND v.auth_status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['video_count']


def get_video_list_by_auth_status(status, count, request=None):
    """ 根据审核状态获取视频列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      v.*,
                    u.avatar,
                    u.nickname
        FROM        cmf_vchat_videos AS v
        JOIN        cmf_users AS u ON v.uid=u.id
        WHERE       {apps_condition} AND
                    auth_status={status}
        ORDER BY    update_time DESC
        LIMIT       {count};
    '''
    records = db.query_data_from_db(sql)
    if records is None:
        return []

    for record in records:
        record['star_desp'] = '⭐️'*record['star']
    return records


def get_video_list_by_star(star, count, request=None):
    """ 根据星级取视频列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      v.*,
                    u.avatar,
                    u.nickname
        FROM        cmf_vchat_videos AS v
        JOIN        cmf_users AS u ON v.uid=u.id
        WHERE       {apps_condition} AND
                    star={star} AND
                    v.auth_status={AUTH_STATUS_CERTIFIED}
        ORDER BY    update_time DESC
        LIMIT       {count};
    '''
    records = db.query_data_from_db(sql)
    if records is None:
        return []

    for record in records:
        record['star_desp'] = '⭐️'*record['star']
    return records


def get_video_list_sort_by_price_desc(count, request=None):
    """ 根据视频价格倒序提取视频列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      v.*,
                    u.avatar,
                    u.nickname
        FROM        cmf_vchat_videos AS v
        JOIN        cmf_users AS u ON v.uid=u.id
        WHERE       {apps_condition} AND
                    v.auth_status={AUTH_STATUS_CERTIFIED}
        ORDER BY    v.price DESC, update_time DESC
        LIMIT       {count};
    '''
    records = db.query_data_from_db(sql)
    if records is None:
        return []

    for record in records:
        record['star_desp'] = '⭐️'*record['star']
        # record['cover_url'] = record['video_url'] + '?vframe/png/offset/0'
    return records


def get_video_info(vid):
    """ 根据id查找视频信息 """
    sql = f'''
        SELECT      *
        FROM        cmf_vchat_videos
        WHERE       id={vid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    if not data:
        return None

    video = data[0]
    video['star_desp'] = '⭐️' * video['star']
    return video


def update_video_auth_status(vid, auth_status):
    """ 更新视频的认证状态 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_vchat_videos
        SET         auth_status={auth_status},
                    update_time="{now}"
        WHERE       id={vid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def update_video_star(vid, star):
    """ 更新视频的星级 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_vchat_videos
        SET         star={star},
                    update_time="{now}"
        WHERE       id={vid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_user_waiting_auth_video_count(uid):
    """ 获取用户待审核的视频数量 """
    sql = f'''
        SELECT      COUNT(*) AS video_count
        FROM        cmf_vchat_videos AS v
        JOIN        cmf_users_auth AS a ON v.uid=a.uid
        WHERE       v.uid={uid} AND
                    v.auth_status={AUTH_STATUS_WAITING} AND
                    a.status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['video_count'] if data else 0


def get_user_video_count(uid):
    """ 获取用户的视频数量 """
    sql = f'''
        SELECT      COUNT(*) AS video_count
        FROM        cmf_vchat_videos AS v
        WHERE       v.uid={uid};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['video_count'] if data else 0


def get_user_video_list(uid):
    """ 获取用户的视频列表 """
    sql = f'''
        SELECT      *
        FROM        cmf_vchat_videos AS v
        WHERE       v.uid={uid}
        ORDER BY    v.auth_status;
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def add_video_for_user(uid, cover_url, video_url):
    """ 为用户增加视频 """
    sql = '''
        INSERT INTO cmf_vchat_videos
        SET         uid=%(uid)s,
                    cover_url=%(cover_url)s,
                    video_url=%(video_url)s,
                    auth_status=%(auth_status)s;
    '''
    params = {
        'uid': str(uid),
        'cover_url': cover_url,
        'video_url': video_url,
        'auth_status': str(VIDEO_AUTH_STATUS_WAITING),
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def update_calling_video(vid, is_calling_video):
    """ 更新视频是否为呼叫视频 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_vchat_videos
        SET         is_calling_video={is_calling_video},
                    update_time="{now}"
        WHERE       id={vid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


if __name__ == '__main__':
    print('ok')
